package com.niit.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.niit.model.Book;
import com.niit.util.JDBCUtil;

public class BookDao {
	Connection conn = null;
	PreparedStatement ps = null;

	public BookDao() {
		conn = JDBCUtil.getConnection();
	}

	public List<Book> queryBooks() {
		List<Book> lstBooks = new ArrayList<Book>();
		ResultSet rs = null;

		try {
			String sql = "select * from book";
			ps = conn.prepareStatement(sql);
			System.out.println(ps.toString());
			rs = ps.executeQuery();

			while (rs.next()) {
				Book book = new Book();
				book.setId(rs.getInt("id"));
				book.setCode(rs.getString("code"));
				book.setName(rs.getString("name"));
				book.setPrice(rs.getBigDecimal("price"));
				book.setAuthor(rs.getString("author"));
				book.setStock(rs.getInt("stock"));
				book.setPublisher(rs.getString("publisher"));
				book.setDescription(rs.getString("description"));
				lstBooks.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.closeDB(conn, ps, rs);
		}
		return lstBooks;
	}

	public Book getBookByCode(String code) {
		List<Book> lstBooks = new ArrayList<Book>();
		ResultSet rs = null;
		try {
			String sql = "select * from book where code = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, code);
			rs = ps.executeQuery();
			while (rs.next()) {
				Book book = new Book();
				book.setId(rs.getInt("id"));
				book.setCode(rs.getString("code"));
				book.setName(rs.getString("name"));
				book.setPrice(rs.getBigDecimal("price"));
				book.setAuthor(rs.getString("author"));
				book.setStock(rs.getInt("stock"));
				book.setPublisher(rs.getString("publisher"));
				book.setDescription(rs.getString("description"));
				lstBooks.add(book);
			}
			if (lstBooks.size() > 0) {
				return lstBooks.get(0);
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return null;
	}

	public void addBook(Book book) {
		try {
			String sql = "insert into book (code,name,price,author,stock,publisher,description) values (?,?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, book.getCode());
			ps.setString(2, book.getName());
			ps.setBigDecimal(3, book.getPrice());
			ps.setString(4, book.getAuthor());
			ps.setInt(5, book.getStock());
			ps.setString(6, book.getPublisher());
			ps.setString(7, book.getDescription());
			int count = ps.executeUpdate();
			if (count > 0) {
				System.out.println("新增书籍成功");
			} else {
				System.out.println("新增书籍失败");
			}

		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.closeDB(conn, ps);
		}

	}

	public Book getBookById(Integer id) {
		List<Book> lstBooks = new ArrayList<Book>();
		ResultSet rs = null;
		try {
			String sql = "select * from book where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs = ps.executeQuery();
			while (rs.next()) {
				Book book = new Book();
				book.setId(rs.getInt("id"));
				book.setCode(rs.getString("code"));
				book.setName(rs.getString("name"));
				book.setPrice(rs.getBigDecimal("price"));
				book.setAuthor(rs.getString("author"));
				book.setStock(rs.getInt("stock"));
				book.setPublisher(rs.getString("publisher"));
				book.setDescription(rs.getString("description"));
				lstBooks.add(book);
			}
			if (lstBooks.size() > 0) {
				return lstBooks.get(0);
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		}

		return null;
	}

	public void deleteBookById(Integer id) {
		try {
			String sql = "delete from book where id = ?";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, id);
			int count = ps.executeUpdate();
			if (count > 0) {
				System.out.println("删除书籍成功");
			} else {
				System.out.println("删除书籍失败");
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.closeDB(conn, ps);
		}

	}

	public void updateBook(Book book) {
		try {
			String sql = "update book set code=?,name=?,price=?,author=?,stock=?,publisher=?,description=? where id=?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, book.getCode());
			ps.setString(2, book.getName());
			ps.setBigDecimal(3, book.getPrice());
			ps.setString(4, book.getAuthor());
			ps.setInt(5, book.getStock());
			ps.setString(6, book.getPublisher());
			ps.setString(7, book.getDescription());
			ps.setInt(8, book.getId());
			int count = ps.executeUpdate();
			if (count > 0) {
				System.out.println("更新书籍成功");
			} else {
				System.out.println("更新书籍失败");
			}
		} catch (SQLException e) {
			// TODO: handle exception
			e.printStackTrace();
		} finally {
			JDBCUtil.closeDB(conn, ps);
		}

	}

	public List<Book> queryBooksByCondition(Book queryBook) {
		List<Book> lstBooks = new ArrayList<Book>();
		Statement statement = null;
		ResultSet rs = null;

		try {
			StringBuilder builder = new StringBuilder("select * from book where 1=1 ");
			// 判断查询条件,动态拼接查询条件
			String code = queryBook.getCode();
			String name = queryBook.getName();
			if (code != null && "" != code) {
				builder.append(" and code like '%" + code + "%' ");
			}
			if (name != null && "" != name) {
				builder.append(" and name like '%" + name + "%' ");
			}
			String sql = builder.toString();
			System.out.println(sql);
			statement = conn.createStatement();
			rs = statement.executeQuery(sql);
			while (rs.next()) {
				Book book = new Book();
				book.setId(rs.getInt("id"));
				book.setCode(rs.getString("code"));
				book.setName(rs.getString("name"));
				book.setPrice(rs.getBigDecimal("price"));
				book.setAuthor(rs.getString("author"));
				book.setStock(rs.getInt("stock"));
				book.setPublisher(rs.getString("publisher"));
				book.setDescription(rs.getString("description"));
				lstBooks.add(book);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.closeDB(conn, statement, rs);
		}
		return lstBooks;
	}

}
